Shrink i wolne miejsce w tabelach


sql / as sysdba

ALTER SESSION SET CONTAINER=PDB1;
Session altered.

SQL> CREATE TABLE HR.EMPLOYEE_DATA (
    employee_id    NUMBER(10),
    first_name     VARCHAR2(50),
    last_name      VARCHAR2(50),
    hire_date      DATE,
    salary         NUMBER(10,2)
);


BEGIN
    FOR i IN 1..100000 LOOP
        INSERT INTO HR.EMPLOYEE_DATA (employee_id, first_name, last_name, hire_date, salary)
        VALUES (i, 'Jan' || i, 'Kowalski' || i, SYSDATE - DBMS_RANDOM.VALUE(0, 3650), DBMS_RANDOM.VALUE(3000, 10000));
    END LOOP;
    COMMIT;
END;
/



SELECT COUNT(*) FROM HR.EMPLOYEE_DATA;

SELECT * FROM HR.EMPLOYEE_DATA FETCH FIRST 10 ROWS ONLY;

SELECT SEGMENT_NAME, BYTES / 1024 / 1024 AS MB FROM DBA_SEGMENTS WHERE  SEGMENT_NAME = 'EMPLOYEE_DATA' AND OWNER = 'HR';

DELETE FROM HR.EMPLOYEE_DATA WHERE employee_id < 90000;

SELECT SEGMENT_NAME, BYTES / 1024 / 1024 AS MB FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'EMPLOYEE_DATA' AND OWNER = 'HR';

ALTER TABLE HR.EMPLOYEE_DATA ENABLE ROW MOVEMENT;

ALTER TABLE HR.EMPLOYEE_DATA SHRINK SPACE;

ALTER TABLE HR.EMPLOYEE_DATA DISABLE ROW MOVEMENT;

SELECT SEGMENT_NAME, BYTES / 1024 / 1024 AS MB FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'EMPLOYEE_DATA' AND OWNER = 'HR';




Ponowna kompilacja obiektów

SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS WHERE STATUS = 'INVALID';

EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => 'HR');

@?/rdbms/admin/utlrp.sql





Zbieranie statystyk

sql / as sysdba

ALTER SESSION SET CONTAINER=PDB1;

CREATE TABLE HR.EMPLOYEE_DATA2 (
    employee_id    NUMBER(10),
    first_name     VARCHAR2(50),
    last_name      VARCHAR2(50),
    hire_date      DATE,
    salary         NUMBER(10,2)
);


BEGIN
    FOR i IN 1..100000 LOOP
        INSERT INTO HR.EMPLOYEE_DATA2 (employee_id, first_name, last_name, hire_date, salary)
        VALUES (i, 'Jan' || i, 'Kowalski' || i, SYSDATE - DBMS_RANDOM.VALUE(0, 3650), DBMS_RANDOM.VALUE(3000, 10000));
    END LOOP;
    COMMIT;
END;
/


SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN FROM DBA_TABLES WHERE TABLE_NAME = 'EMPLOYEE_DATA2' AND OWNER='HR';

SELECT owner, table_name, TO_CHAR(last_analyzed,'YYYY-MM-DD HH24:MI:SS') AS last_analyzed FROM  DBA_TAB_STATISTICS WHERE owner = 'HR' AND  table_name = 'EMPLOYEE_DATA2';

EXECUTE DBMS_STATS.GATHER_TABLE_STATS('HR','EMPLOYEE_DATA2');

SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN FROM DBA_TABLES WHERE TABLE_NAME = 'EMPLOYEE_DATA2' AND OWNER='HR';

SELECT owner, table_name, TO_CHAR(last_analyzed,'YYYY-MM-DD HH24:MI:SS') AS last_analyzed FROM  DBA_TAB_STATISTICS WHERE owner = 'HR' AND  table_name = 'EMPLOYEE_DATA2';

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');

EXECUTE DBMS_STATS.GATHER_DATABASE_STATS;

EXEC DBMS_STATS.GATHER_DATABASE_STATS(degree => 4);

EXEC DBMS_STATS.GATHER_INDEX_STATS('HR', 'EMP_DEPARTMENT_IX');

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -b gatherstats -- --x"exec dbms_stats.gather_dictionary_stats"

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -c PDB1 -b gatherstats -- --x"exec dbms_stats.gather_dictionary_stats"





Narzędzie DBNEWID (nid)

sql / as sysdba

SELECT DBID, NAME, DB_UNIQUE_NAME FROM V$DATABASE;

SHUTDOWN IMMEDIATE

STARTUP MOUNT

nid TARGET=/ DBNAME=db024t

sql / as sysdba

STARTUP NOMOUNT

ALTER SYSTEM SET db_name='DB024T' SCOPE=SPFILE;

ALTER SYSTEM SET db_unique_name='DB024T' SCOPE=SPFILE;

SHUTDOWN IMMEDIATE

STARTUP MOUNT

SHUTDOWN IMMEDIATE

cd $ORACLE_HOME/dbs

mv initdb024p.ora initdb024t.ora
mv orapwdb024p orapwdb024t
mv spfiledb024p.ora spfiledb024t.ora

export ORACLE_SID=db024t
export ORACLE_UNQNAME=db024t

. ~/.bash_profile

lsnrctl reload

sql / as sysdba

STARTUP

SELECT DBID, NAME, DB_UNIQUE_NAME FROM V$DATABASE;

